# -*- coding: utf-8 -*-

from odoo import models, fields, api
from datetime import datetime, timedelta
from dateutil.relativedelta import relativedelta
import psycopg2
import os
import sys
import logging
import calendar
import ast
import time

db_name = "admin"
db_user = "liuhuan"
db_pass = "123456"
db_ip = "127.0.0.1"
error_log = "____"
# 定义日志输出格式
logging.basicConfig(level=logging.ERROR,
                    format='%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s',
                    datefmt='%Y-%m-%d %H:%M:%S',
                    filename=error_log,
                    filemode='a')


def writeDb(sql, data):
    """
    连接mysql数据库（写），并进行写的操作，如果连接失败，会把错误写入日志中，并返回false，如果sql执行失败，也会把错误写入日志中，并返回false，如果所有执行正常，则返回true
    """
    try:
        conn = psycopg2.connect(database=db_name, user=db_user, password=db_pass, host=db_ip, port=5432)
        cursor = conn.cursor()
        print '数据库连接成功'
    except Exception, e:
        print e
        logging.error('数据库连接失败:%s' % e)
        return False
    try:
        cursor.execute(sql, data)
        conn.commit()  # 提交事务
    except Exception, e:
        conn.rollback()  # 如果出错，则事务回滚
        logging.error('数据写入失败:%s' % e)
        return False
    finally:
        cursor.close()
        conn.close()
    print '数据库写数据成功'
    return True


def readDb(sql):
    """
    连接mysql数据库（从），并进行数据查询，如果连接失败，会把错误写入日志中，并返回false，如果sql执行失败，也会把错误写入日志中，并返回false，如果所有执行正常，则返回查询到的数据，这个数据是经过转换的，转成字典格式，方便模板调用，其中字典的key是数据表里的字段名
    """
    try:
        conn = psycopg2.connect(database=db_name, user=db_user, password=db_pass, host=db_ip, port=5432)
        cursor = conn.cursor()
    except Exception, e:
        print e
        logging.error('数据库连接失败:%s' % e)
        return False
    try:
        cursor.execute(sql)
        data = [dict((cursor.description[i][0], value) for i, value in enumerate(row)) for row in
                cursor.fetchall()]  # 转换数据，字典格式
    except Exception, e:
        logging.error('数据执行失败:%s' % e)
        return False
    finally:
        cursor.close()
        conn.close()
    return data


class Temporary(models.Model):
    _name = 'temporary.data'

    name = fields.Char()

    sale_order_id = fields.Integer("销售订单")
    partner = fields.Char("客户")
    saleman = fields.Char("销售员")
    crm_team = fields.Char("销售团队")

    # sale_order
    confirmation_date = fields.Datetime("确认日期")
    subscription_period = fields.Char("认购期限")
    amount_untaxed = fields.Float("未含税金额")
    amount_total = fields.Float("总计")
    invoice_status = fields.Char("发票状态")
    annual_interest = fields.Float("年利率")

    # product_template
    product_name = fields.Char("产品名")
    list_price = fields.Float("标价")
    is_financial = fields.Boolean("金融产品")
    sale_bonus = fields.Float("产品提成")
    type_financial = fields.Char("金融产品类型")

    # sale_order_line
    product_uom_qty = fields.Float("订购数量")
    qty_invoiced = fields.Float("已开发票数量")
    price_unit = fields.Float("单价")
    product_description = fields.Char("产品说明")

    # account_invoice
    invoice_number = fields.Char("发票号码")
    date_invoice = fields.Date("开票日期")
    origin = fields.Char("源单据")
    date_due = fields.Date("截止日期")
    residual = fields.Float("截止金额")

    # 新增还款四个字段
    repayment_times_total = fields.Integer("还款总期数")
    repayment_time = fields.Integer("第几期还款")
    repayment_datetime = fields.Datetime("本期还款日期")
    repayment_amount = fields.Float("本期还款数额", readonly=True)
    repayment_status = fields.Boolean("还款状态", default=False)


def cal():
    #以单据日期为购买的确认时间
    sql = "SELECT public.sale_order.id AS 销售订单, " \
          "public.sale_order.date_order AS 确认日期, " \
          "public.sale_order.subscription_period AS 认购期限, " \
          "public.sale_order.amount_untaxed AS 未含税金额, " \
          "public.sale_order.amount_total AS 总计, " \
          "public.sale_order.invoice_status AS 发票状态, " \
          "public.sale_order.annual_interest AS 年利率, " \
          "public.res_partner.name AS 客户, " \
          "public.crm_team.name AS 销售团队, " \
          "public.product_template.name AS 产品名,	" \
          "public.product_template.list_price AS 标价,	" \
          "public.product_template.is_financial AS 金融产品, " \
          "public.product_template.sale_bonus AS 产品提成, " \
          "public.product_template.type_financial AS 金融产品类型, " \
          "public.sale_order_line.product_uom_qty AS 订购数量, " \
          "public.sale_order_line.qty_invoiced AS 已开发票数量, " \
          "public.sale_order_line.price_unit AS 单价, " \
          "public.sale_order_line.name AS 产品说明, " \
          "public.account_invoice.number AS 发票号码, " \
          "public.account_invoice.date_invoice AS 开票日期, " \
          "public.account_invoice.origin AS 源单据, " \
          "public.account_invoice.date_due AS 截止日期, " \
          "public.account_invoice.residual AS 截止金额 " \
          "FROM public.sale_order " \
          "INNER JOIN public.sale_order_line ON public.sale_order_line.order_id = public.sale_order.id " \
          "INNER JOIN public.res_partner ON public.sale_order.partner_id = public.res_partner.id " \
          "INNER JOIN public.product_product ON public.sale_order_line.product_id = public.product_product.id " \
          "INNER JOIN public.product_template ON public.product_product.product_tmpl_id = public.product_template.id " \
          "INNER JOIN public.res_users ON public.sale_order.user_id = public.res_users.id " \
          "LEFT JOIN public.crm_team ON public.crm_team.id = public.res_users.sale_team_id " \
          "LEFT JOIN public.account_invoice ON public.account_invoice.partner_id = public.res_partner.id " \
          "WHERE public.sale_order.invoice_status = 'invoiced' AND public.sale_order.repayment_status = FALSE AND public.account_invoice.state = 'paid'"
    data = readDb(sql)
    for i in range(len(data)):
        if data[i]["金融产品类型"] == '2':  # 基金
            fund(data, i)
            sql2 = "UPDATE public.sale_order SET repayment_status = TRUE WHERE id = %s" % data[i]["销售订单"]
            writeDb(sql2, data)
        if data[i]["金融产品类型"] == '1':  # 借款
            loan(data, i)
            sql2 = "UPDATE public.sale_order SET repayment_status = TRUE WHERE id = %s" % data[i]["销售订单"]
            writeDb(sql2, data)
        if data[i]["金融产品类型"] == '3':  # 其他
            other(data, i)
            sql2 = "UPDATE public.sale_order SET repayment_status = TRUE WHERE id = %s" % data[i]["销售订单"]
            writeDb(sql2, data)


def fund(data, i):#基金
    confirm_date = datetime.strptime(data[i]["确认日期"], "%Y-%m-%d %H:%M:%S")
    first_repay_date = confirm_date + relativedelta(months=+3) + relativedelta(days=-1)  # 第一次还款日期
    periods = int(data[i]["认购期限"]) / 3  # 还款次数
    interest = data[i]["总计"] * ast.literal_eval(data[i]["年利率"]) / 12 * 3  # 利息
    principal_interest = data[i]["总计"] + interest  # 本息
    for period in range(periods):
        repay_date = first_repay_date + relativedelta(months=+3) * period
        sql = "insert into public.temporary_data(" \
              "sale_order_id, partner, crm_team, confirmation_date, subscription_period, amount_untaxed, " \
              "amount_total, invoice_status, annual_interest, product_name, list_price, is_financial, " \
              "sale_bonus, type_financial, product_uom_qty, qty_invoiced, price_unit, product_description, " \
              "invoice_number, date_invoice, origin, date_due, residual, " \
              "repayment_times_total, repayment_time, repayment_datetime, repayment_amount)" \
              "values(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
        if (period == (periods - 1)):
            data1 = (
                data[i]["销售订单"], data[i]["客户"], data[i]["销售团队"], data[i]["确认日期"], data[i]["认购期限"], data[i]["未含税金额"],
                data[i]["总计"], data[i]["发票状态"], data[i]["年利率"], data[i]["产品名"], data[i]["标价"], data[i]["金融产品"],
                data[i]["产品提成"], data[i]["金融产品类型"], data[i]["订购数量"], data[i]["已开发票数量"], data[i]["单价"], data[i]["产品说明"],
                data[i]["发票号码"], data[i]["开票日期"], data[i]["源单据"], data[i]["截止日期"], data[i]["截止金额"],
                periods, periods, repay_date, principal_interest)
            writeDb(sql, data1)
        else:
            data1 = (
                data[i]["销售订单"], data[i]["客户"], data[i]["销售团队"], data[i]["确认日期"], data[i]["认购期限"], data[i]["未含税金额"],
                data[i]["总计"], data[i]["发票状态"], data[i]["年利率"], data[i]["产品名"], data[i]["标价"], data[i]["金融产品"],
                data[i]["产品提成"], data[i]["金融产品类型"], data[i]["订购数量"], data[i]["已开发票数量"], data[i]["单价"], data[i]["产品说明"],
                data[i]["发票号码"], data[i]["开票日期"], data[i]["源单据"], data[i]["截止日期"], data[i]["截止金额"],
                periods, (period + 1), repay_date, interest)
            writeDb(sql, data1)

def loan(data, i):# 借款
    confirm_date = datetime.strptime(data[i]["确认日期"], "%Y-%m-%d %H:%M:%S")
    if confirm_date.day > 10 & confirm_date.day <= 25:
        first_repay_date = confirm_date.replace(day=25) + relativedelta(months=+1)  # 第一次还款日期
    else:
        first_repay_date = confirm_date.replace(day=10) + relativedelta(months=+1)  # 第一次还款日期
    periods = int(data[i]["认购期限"])  # 还款次数
    leapyear_first_interest = data[i]["总计"] * ast.literal_eval(data[i]["年利率"]) / 366 * (
        (first_repay_date - confirm_date).days)  # 闰年第一个月利息
    notleapyear_first_interest = data[i]["总计"] * ast.literal_eval(data[i]["年利率"]) / 365 * (
        (first_repay_date - confirm_date).days)  # 非闰年第一个月利息
    interest = data[i]["总计"] * ast.literal_eval(data[i]["年利率"]) / 12  # 利息
    for period in range(periods):
        repay_date = first_repay_date + relativedelta(months=+1) * period
        second_repay_date = first_repay_date + relativedelta(months=+1)
        principal_interest = data[i]["总计"] * ast.literal_eval(data[i]["年利率"]) / 12 * periods - notleapyear_first_interest - interest - interest * ((second_repay_date-first_repay_date).days/30-1)+data[i]["总计"]
        sql = "insert into public.temporary_data(" \
              "sale_order_id, partner, crm_team, confirmation_date, subscription_period, amount_untaxed, " \
              "amount_total, invoice_status, annual_interest, product_name, list_price, is_financial, " \
              "sale_bonus, type_financial, product_uom_qty, qty_invoiced, price_unit, product_description, " \
              "invoice_number, date_invoice, origin, date_due, residual, " \
              "repayment_times_total, repayment_time, repayment_datetime, repayment_amount)" \
              "values(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
        if (period == (periods - 1)):
            data1 = (
                data[i]["销售订单"], data[i]["客户"], data[i]["销售团队"], data[i]["确认日期"], data[i]["认购期限"], data[i]["未含税金额"],
                data[i]["总计"], data[i]["发票状态"], data[i]["年利率"], data[i]["产品名"], data[i]["标价"], data[i]["金融产品"],
                data[i]["产品提成"], data[i]["金融产品类型"], data[i]["订购数量"], data[i]["已开发票数量"], data[i]["单价"], data[i]["产品说明"],
                data[i]["发票号码"], data[i]["开票日期"], data[i]["源单据"], data[i]["截止日期"], data[i]["截止金额"],
                periods, periods, repay_date, principal_interest)
            writeDb(sql, data1)
        elif (period == 0):
            if (confirm_date.year % 4) == 0 & (confirm_date.year % 100) == 0 & (confirm_date.year % 400) == 0 | (
                        confirm_date.year % 4) == 0 & (confirm_date.year % 100) != 0:
                data1 = (
                    data[i]["销售订单"], data[i]["客户"], data[i]["销售团队"], data[i]["确认日期"], data[i]["认购期限"], data[i]["未含税金额"],
                    data[i]["总计"], data[i]["发票状态"], data[i]["年利率"], data[i]["产品名"], data[i]["标价"], data[i]["金融产品"],
                    data[i]["产品提成"], data[i]["金融产品类型"], data[i]["订购数量"], data[i]["已开发票数量"], data[i]["单价"],
                    data[i]["产品说明"],
                    data[i]["发票号码"], data[i]["开票日期"], data[i]["源单据"], data[i]["截止日期"], data[i]["截止金额"],
                    periods, (period + 1), first_repay_date, leapyear_first_interest)
                writeDb(sql, data1)
            else:
                data1 = (
                    data[i]["销售订单"], data[i]["客户"], data[i]["销售团队"], data[i]["确认日期"], data[i]["认购期限"], data[i]["未含税金额"],
                    data[i]["总计"], data[i]["发票状态"], data[i]["年利率"], data[i]["产品名"], data[i]["标价"], data[i]["金融产品"],
                    data[i]["产品提成"],
                    data[i]["金融产品类型"], data[i]["订购数量"], data[i]["已开发票数量"], data[i]["单价"],
                    data[i]["产品说明"], data[i]["发票号码"], data[i]["开票日期"], data[i]["源单据"], data[i]["截止日期"], data[i]["截止金额"],
                    periods, (period + 1), first_repay_date, notleapyear_first_interest)
                writeDb(sql, data1)
        else:
            data1 = (
                data[i]["销售订单"], data[i]["客户"], data[i]["销售团队"], data[i]["确认日期"], data[i]["认购期限"], data[i]["未含税金额"],
                data[i]["总计"], data[i]["发票状态"], data[i]["年利率"], data[i]["产品名"], data[i]["标价"], data[i]["金融产品"],
                data[i]["产品提成"], data[i]["金融产品类型"], data[i]["订购数量"], data[i]["已开发票数量"], data[i]["单价"], data[i]["产品说明"],
                data[i]["发票号码"], data[i]["开票日期"], data[i]["源单据"], data[i]["截止日期"], data[i]["截止金额"],
                periods, (period + 1), repay_date, interest)
            writeDb(sql, data1)

def other(data, i):   #其他
    confirm_date = datetime.strptime(data[i]["确认日期"], "%Y-%m-%d %H:%M:%S")

    if confirm_date.day > 10 & confirm_date.day <= 25:
        first_repay_date = confirm_date.replace(day=25) + relativedelta(months=+int(data[i]["认购期限"]) )  # 第一次还款日期
    else:
        first_repay_date = confirm_date.replace(day=10) + relativedelta(months=+int(data[i]["认购期限"]) )  # 第一次还款日期

    sql = "insert into public.temporary_data(" \
          "sale_order_id, partner, crm_team, confirmation_date, subscription_period, amount_untaxed, " \
          "amount_total, invoice_status, annual_interest, product_name, list_price, is_financial, " \
          "sale_bonus, type_financial, product_uom_qty, qty_invoiced, price_unit, product_description, " \
          "invoice_number, date_invoice, origin, date_due, residual, " \
          "repayment_times_total, repayment_time, repayment_datetime, repayment_amount)" \
          "values(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"

    data1 = (
        data[i]["销售订单"], data[i]["客户"], data[i]["销售团队"], data[i]["确认日期"], data[i]["认购期限"], data[i]["未含税金额"],
        data[i]["总计"], data[i]["发票状态"], data[i]["年利率"], data[i]["产品名"], data[i]["标价"], data[i]["金融产品"],
        data[i]["产品提成"], data[i]["金融产品类型"], data[i]["订购数量"], data[i]["已开发票数量"], data[i]["单价"], data[i]["产品说明"],
        data[i]["发票号码"], data[i]["开票日期"], data[i]["源单据"], data[i]["截止日期"], data[i]["截止金额"],
        0, 0, first_repay_date, 0)
    writeDb(sql, data1)

if __name__ == '__main__':
    while True:
        time.sleep(5)
        print "*** 如果想停止该程序，可以“ctrl”+”C"

        cal()
